package com.bestseller.poi.service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.InputStream;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.LinkedList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.bestseller.poi.entity.Campaign;
import com.bestseller.poi.entity.Repeat;
import com.bestseller.poi.mapper.CampaignMapper;
import com.bestseller.poi.mapper.RepeatMapper;

/** 
* @author  xgx@bestseller.com.cn 
* @date 创建时间：2016年5月5日 下午3:26:52 
* @version 1.0  
*/
@Service
public class RepeatCampaignService {
	@Autowired
	private RepeatMapper repeatMapper;
	@Autowired
	private CampaignMapper campaignMapper;
	
	private List<String> repeatExec(Sheet sheet){
		List<Repeat> repeatss = new ArrayList<>();
		List<String> errors = parseToRepeatList(sheet, repeatss);
		if(errors.size()<=0){
			// 4. 批量插入
			Repeat repeat=null;
			for(int i=0;i<repeatss.size();i++){
				repeat=repeatss.get(i);
				if(repeatMapper.getById(repeat).size()>0){
					repeatMapper.deleteAll(repeat);
					continue;
				}
			}
			
			if(repeatss.size()>500){
				List<Repeat> repeates=new LinkedList<Repeat>();
				for(int i=0;i<repeatss.size();i++){
					repeates.add(repeatss.get(i));
					if(i%500==0){
						repeatMapper.batchSave(repeates);
						repeates.clear();
					}
					if(i==repeatss.size()-1){
						repeatMapper.batchSave(repeates);
					}
				}
			}else{
				repeatMapper.batchSave(repeatss);
			}
			return new ArrayList<>();
		}else{
			return errors;
		}
		
	}
	
	//方法的定义
	private List<String> campaignExec(Sheet sheet){
		// 3. 解析数据: 解析每一行, 每一个单元格.
		List<Campaign> campaignss = new ArrayList<>();
		List<String> errors = parseToCampaignList(sheet, campaignss);
		if(errors.size()<=0){
			// 4. 批量插入
			Campaign campaign=null;
			for(int i=0;i<campaignss.size();i++){
				campaign=campaignss.get(i);
				if(campaignMapper.getById(campaign).size()>0){
					campaignMapper.deleteAll(campaign);
					continue;
				}
			}
			
			if(campaignss.size()>500){
				List<Campaign> campaignes = new LinkedList<Campaign>();
				for(int i=0;i<campaignss.size();i++){
					campaignes.add(campaignss.get(i));
					if(i%500==0){
						campaignMapper.batchSave(campaignes);
						campaignes.clear();
					}
					if(i==campaignss.size()-1){
						campaignMapper.batchSave(campaignes);
					}
				}
			}else{
				campaignMapper.batchSave(campaignss);
			}
			return new ArrayList<>();
		}else{
			return errors;
		}
		
	}
	@Transactional
	public List<String> upload(File file) {
		InputStream is=null;
		try {
			is = new FileInputStream(file);
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		}
		Workbook wb=null;
		try {
			wb = WorkbookFactory.create(is);
		} catch (Exception e) {
			e.printStackTrace();
		} 
		// 2. 得到 Sheet 对象
		Sheet sheet1 = null;
		sheet1 = wb.getSheet("翻单款区分");
		Sheet sheet2 = null;
		sheet2=wb.getSheet("活动款区分");
		if(sheet1!=null&&sheet2!=null){
			repeatExec(sheet1);
			campaignExec(sheet2);
			return new ArrayList<>();
		}
		else if(sheet1!=null&&sheet2==null){
			List<String> errors = repeatExec(sheet1);
			return errors;
		}
		
		else if(sheet2!=null&&sheet1==null){
			List<String> campaignExec = campaignExec(sheet2);
			return campaignExec;
		}
		
		return null;
	}

	
	//表ODS_Inventory_Campaign_Stylecolor的解析
	private List<String> parseToCampaignList(Sheet sheet, List<Campaign> campaignes) {
		List<String> errors=new ArrayList<>();
		for (int i = 2; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			Campaign campaign = parseToCampaign(row,i+1,errors);
			if (campaign != null&&campaign.getShopCode()!=null) {
				campaignes.add(campaign);
			}
		}
		return errors;
	}
	private Campaign parseToCampaign(Row row,int i,List<String> errors) {
		Campaign campaign=null;
		Cell cell = row.getCell(0);
		String stylecolor=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			stylecolor = getCellValue(cell);
		}else{
			errors.add("导入失败,stylecolor为空,第"+i+"行第1列");
		}
		
		cell = row.getCell(1);
		String shopCode=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			shopCode = getCellValue(cell);
		}else{
			errors.add("导入失败,店铺代码为空,第"+i+"行第2列");
		}
		
		cell = row.getCell(2);
		String campaignStartDate=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			campaignStartDate = getCellValue(cell);
		}
		
		cell = row.getCell(3);
		String campaignEndDate=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			campaignEndDate = getCellValue(cell);
		}
		
		cell = row.getCell(4);
		String campaignNam=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			campaignNam = getCellValue(cell);
		}
		
		cell = row.getCell(5);
		String campaignItemCategory=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			campaignItemCategory = getCellValue(cell);
		}
		
		cell = row.getCell(6);
		String jhs=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			jhs =getCellValue(cell);
		}
		
		cell = row.getCell(7);
		Double disPlan=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			try {
				disPlan = Double.parseDouble(getCellValue(cell));
			} catch (NumberFormatException e) {
				errors.add("数据类型错误,第"+i+"行第8列");
			}
		}
		
		cell = row.getCell(8);
		String disPlanDate=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			disPlanDate = getCellValue(cell);
		}
		
		cell = row.getCell(9);
		Double remark1=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			try {
				remark1 = Double.parseDouble(getCellValue(cell));
			} catch (NumberFormatException e) {
				errors.add("数据类型错误,第"+i+"行第10列");
			}
		}
		
		cell = row.getCell(10);
		Double remark2=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			try {
				remark2 = Double.parseDouble(getCellValue(cell)+0);
			} catch (NumberFormatException e) {
				errors.add("数据类型错误,第"+i+"行第11列");
			}
		}
		
		cell = row.getCell(11);
		Double remark3=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			try {
				remark3 = Double.parseDouble(getCellValue(cell)+0);
			} catch (NumberFormatException e) {
				errors.add("数据类型错误,第"+i+"行第12列");
			}
		}
		
		cell = row.getCell(12);
		String remark4=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			remark4 = getCellValue(cell);
		}
		
		cell = row.getCell(13);
		String remark5=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			remark5 = getCellValue(cell);
		}
		
		cell = row.getCell(14);
		String remark6=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			remark6 = getCellValue(cell);
		}
		
		
		campaign=new Campaign(stylecolor, shopCode, campaignStartDate, campaignEndDate, campaignNam, campaignItemCategory, jhs, disPlan, disPlanDate, remark1, remark2, remark3, remark4, remark5, remark6);
		return campaign;
	}


	//表ODS_Inventory_Repeat_Stylecolor的解析
	private List<String> parseToRepeatList(Sheet sheet, List<Repeat> repeates) {
		List<String> errors=new ArrayList<>();
		for (int i = 2; i <= sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i);
			Repeat repeat = parseToRepeat(row,i+1,errors);
			if (repeat != null) {
				repeates.add(repeat);
			}
		}
		return errors;
	}
	
	private Repeat parseToRepeat(Row row,int i,List<String> errors) {
		Repeat repeat=null;
		Cell cell = row.getCell(0);
		String stylecolor=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			stylecolor = getCellValue(cell);
		}
		
		cell = row.getCell(1);
		String repeatType=null;
		if(cell!=null&&getCellValue(cell)!=null&&!getCellValue(cell).equals("")){
			repeatType = getCellValue(cell);
		}
		
		
		repeat=new Repeat(stylecolor, repeatType);
		return repeat;
	}
	
	
	
	//解析单元格 的类型
	private DateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
	private String getCellValue(Cell cell) {
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			return cell.getRichStringCellValue().getString();
		case Cell.CELL_TYPE_NUMERIC:
			if (DateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
				return dateFormat.format(date)+"";
			} else {
				return cell.getNumericCellValue() + "";
			}
		case Cell.CELL_TYPE_BOOLEAN:
			return cell.getBooleanCellValue() + "";
		case Cell.CELL_TYPE_FORMULA:
			return cell.getCellFormula() + "";
		}
		return null;
	}
}
